package com.gsbelarus.gedemin.income.sync;

import android.content.ContentValues;
import android.content.Context;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.gsbelarus.gedemin.income.ClientsTableHelper;
import com.gsbelarus.gedemin.income.table.ClientsTable;
import com.gsbelarus.gedemin.income.R;
import com.gsbelarus.gedemin.lib.Utils;
import com.gsbelarus.gedemin.lib.sync.protocol.DataBaseModel;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;

public class DataBaseHelper extends DataBaseModel {

    private static DataBaseHelper mInstance = null;

    private HashMap<Integer, List<Calendar>> possibleDates = null;

    private static final int DATABASE_VERSION = 3;

    public static final String TABLE_CONTACT_NAME = "MB_CONTACT";
    public static final String NAME = "NAME";

    public static final String TABLE_ENTRY_NAME = "MB_ENTRY";
    public static final String UID_ENTRY = "ENTRYID";
    public static final String DATE = "ENTRYDATE";
    public static final String DEBIT = "DEBIT";
    public static final String CREDIT = "CREDIT";
    public static final String DEBIT_NCU = "DEBITNCU";
    public static final String CREDIT_NCU = "CREDITNCU";
    public static final String DEBIT_CONTACT_KEY = "DEBITCONTACTKEY";
    public static final String CREDIT_CONTACT_KEY = "CREDITCONTACTKEY";

    private static final String MB_CONTACT_CREATE = "CREATE TABLE IF NOT EXISTS "
            + TABLE_CONTACT_NAME + " ("
            + UID + " INTEGER PRIMARY KEY,"
            + NAME + " VARCHAR(100)"
            + ");";

    private static final String MB_ENTRY_GREATE = "CREATE TABLE IF NOT EXISTS "
            + TABLE_ENTRY_NAME + " ("
            + UID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + UID_ENTRY + " INTEGER,"
            + DATE + " DATETIME,"
            + DEBIT + " VARCHAR(40) NOT NULL,"
            + CREDIT + " VARCHAR(40) NOT NULL,"
            + DEBIT_NCU + " DECIMAL(15,4) NOT NULL,"
            + CREDIT_NCU + " DECIMAL(15,4) NOT NULL,"
            + DEBIT_CONTACT_KEY + " INTEGER,"
            + CREDIT_CONTACT_KEY + " INTEGER,"
            + "FOREIGN KEY(" + DEBIT_CONTACT_KEY + ") REFERENCES " + TABLE_CONTACT_NAME + "(" + UID + "),"
            + "FOREIGN KEY(" + CREDIT_CONTACT_KEY + ") REFERENCES " + TABLE_CONTACT_NAME + "(" + UID + ")"
            + ");";

    private static final String MB_CONTACT_DELETE = "DROP TABLE IF EXISTS "
            + TABLE_CONTACT_NAME;

    private static final String MB_ENTRY_DELETE = "DROP TABLE IF EXISTS "
            + TABLE_ENTRY_NAME;

    public synchronized static DataBaseHelper getInstance(Context context) {
        if (mInstance == null) {
            mInstance = new DataBaseHelper(context);
        }
        mInstance.open();
        return mInstance;
    }

    private DataBaseHelper(Context context) {
        super(context);
    }

    /**
     * @return номер версии базы данных.
     * Если версия данных больше текущей, вызовится метод onUpgradeDB
     */
    @Override
    protected int getDataBaseVer() {
        return DATABASE_VERSION;
    }

    /**
     * Вызывается при создании базы данных. Внутри следует создать все таблицы
     *
     * @param sqLiteDatabase объект для работы с базой данных
     */
    @Override
    protected void onCreateDB(SQLiteDatabase sqLiteDatabase) {
        sqLiteDatabase.execSQL(MB_CONTACT_CREATE);
        sqLiteDatabase.execSQL(MB_ENTRY_GREATE);
    }

    /**
     * Вызывается после вызова метода recreateDataBase. Внутри следует удалить все таблицы
     *
     * @param sqLiteDatabase объект для работы с базой данных
     */
    @Override
    protected void onDeleteDB(SQLiteDatabase sqLiteDatabase) {
        sqLiteDatabase.execSQL(MB_CONTACT_DELETE);
        sqLiteDatabase.execSQL(MB_ENTRY_DELETE);
    }

    /**
     * Вызывается при несовпадении версий базы данных
     *
     * @param sqLiteDatabase объект для работы с базой данных
     * @param oldVer         старая версия базы данных
     * @param newVer         новая версия базы данных
     */
    @Override
    protected void onUpgradeDB(SQLiteDatabase sqLiteDatabase, int oldVer, int newVer) {
        mDB = sqLiteDatabase;
        recreateDataBase();
        context.startService(new Intent(context, SyncService.class));
    }

    /**
     * пересоздание временных переменных для даты синхронизации, версии данных
     */
    @Override
    public void initTempVar() {
        super.initTempVar();
        if (possibleDates != null)
            possibleDates.clear();
        possibleDates = null;
        getPossibleDatesList(Calendar.DATE);
    }

    /**
     * Заполнение таблиц случайными данными
     */
    @Override
    protected void createDemoDB() {
        String[] clients = new String[]{"Рога и Копыта, ООО", "Кооператив Геркулес", "Амперсант, ООО", "Золотые Программы, ЗАО",
                "Агро-мотоль, ОАО", "Дэйрас, УП", "ИП Безенчук", "Святогор, ООО", "Конкордия, ООО", "Панорама, ЗАО"};

        ContentValues cv = new ContentValues();

        for (int i = 0; i < clients.length; i++) {
            cv.put(DataBaseHelper.UID, i);
            cv.put(DataBaseHelper.NAME, clients[i]);
            mDB.insert(DataBaseHelper.TABLE_CONTACT_NAME, null, cv);
            cv.clear();
        }

        Calendar c = Calendar.getInstance();
        for (int i = 0; i < 12; i++) {
            c.set(Calendar.DAY_OF_MONTH, 1);

            for (int j = 1; j <= c.getActualMaximum(Calendar.DAY_OF_MONTH); j++) {
                c.set(Calendar.DAY_OF_MONTH, j);

                if (c.getTimeInMillis() > System.currentTimeMillis())
                    continue;

                if (c.get(Calendar.DAY_OF_WEEK) != Calendar.SATURDAY &&
                        c.get(Calendar.DAY_OF_WEEK) != Calendar.SUNDAY)
                /** создание проводок для поступлений */
                    for (int iClients = 0; iClients < clients.length; iClients++) {
                        long sum = 100000 + (long) (Math.random() * ((10000000 - 100000) + 1));

                        cv.put(DataBaseHelper.DATE, new SimpleDateFormat("yyyy-MM-dd").format(c.getTimeInMillis()));
                        cv.put(DataBaseHelper.DEBIT, "51");
                        cv.put(DataBaseHelper.CREDIT, 12345679);
                        cv.put(DataBaseHelper.DEBIT_NCU, sum);
                        cv.put(DataBaseHelper.CREDIT_NCU, sum);
                        cv.put(DataBaseHelper.DEBIT_CONTACT_KEY, 12345679);
                        cv.put(DataBaseHelper.CREDIT_CONTACT_KEY, iClients);
                        mDB.insert(DataBaseHelper.TABLE_ENTRY_NAME, null, cv);
                        cv.clear();
                    }

                if (c.get(Calendar.DAY_OF_MONTH) == c.getActualMaximum(Calendar.DAY_OF_MONTH))
                /** создание плановых проводок */
                    for (int iClients = 0; iClients < clients.length; iClients++) {
                        long sum = 50000000 + (long) (Math.random() * ((150000000 - 50000000) + 1));

                        cv.put(DataBaseHelper.DATE, new SimpleDateFormat("yyyy-MM-dd").format(c.getTimeInMillis()));
                        cv.put(DataBaseHelper.DEBIT, "PLAN");
                        cv.put(DataBaseHelper.CREDIT, 12345679);
                        cv.put(DataBaseHelper.DEBIT_NCU, sum);
                        cv.put(DataBaseHelper.CREDIT_NCU, sum);
                        cv.put(DataBaseHelper.DEBIT_CONTACT_KEY, iClients);
                        cv.put(DataBaseHelper.CREDIT_CONTACT_KEY, 123456789);
                        mDB.insert(DataBaseHelper.TABLE_ENTRY_NAME, null, cv);
                        cv.clear();
                    }
            }
            c.add(Calendar.MONTH, -1);
        }

        Log.d("Balance", String.valueOf(getBalance()));
    }

    public List<Calendar> getPossibleDatesList(int slidingDate) {
        if (possibleDates != null)
            return possibleDates.get(slidingDate);

        String sql =
                "SELECT " +
                        "  strftime('%s',entrydate), " +
                        "  SUM(COALESCE(sel.RealSum, 0)), " +
                        "  SUM(COALESCE(sel.PlanSum, 0)) " +
                        "FROM (" +
                        "  SELECT " +
                        "    d.entrydate, " +
                        "    SUM(d.debitncu) AS RealSum, " +
                        "    0 AS PlanSum " +
                        "  FROM mb_contact a " +
                        "  JOIN mb_entry d ON d.creditcontactkey = a.id " +
                        "                 AND d.debit = '51'  " +
                        "  GROUP BY 1,3 " +
                        " " +
                        "  UNION ALL " +
                        " " +
                        "  SELECT " +
                        "    d.entrydate, " +
                        "    0 AS RealSum, " +
                        "    SUM(d.debitncu) AS PlanSum " +
                        "  FROM mb_contact a " +
                        "  JOIN mb_entry d ON d.debitcontactkey = a.id " +
                        "                 AND d.debit = 'PLAN'" +
                        "  GROUP BY 1,2 " +
                        "  HAVING SUM(d.debitncu) > 0) sel " +
                        "GROUP BY 1 " +
                        "HAVING SUM(COALESCE(sel.RealSum, 0)) <> 0 OR SUM(COALESCE(sel.PlanSum, 0)) <> 0 " +
                        "ORDER BY entrydate";

        if (isTransaction) return null;

        Cursor cursor = mDB.rawQuery(sql, null);
        Calendar cal;

        List<Calendar> possibleDays = new ArrayList<Calendar>();
        if (cursor.moveToFirst()) {
            do {
                cal = Calendar.getInstance();
                cal.setTimeInMillis(cursor.getLong(0) * 1000);
                possibleDays.add(cal);
            } while (cursor.moveToNext());

        } else
            possibleDays.add(Calendar.getInstance());

        possibleDates = new HashMap<Integer, List<Calendar>>();

        possibleDates.put(Calendar.DATE, possibleDays);
        possibleDates.put(Calendar.MONTH, calculateSlidingDates(possibleDays, Calendar.MONTH));
        possibleDates.put(Calendar.YEAR, calculateSlidingDates(possibleDays, Calendar.YEAR));

        return possibleDates.get(slidingDate);
    }

    public List<Calendar> getPossibleDatesListByID(int id, int slidingDate) {
        String sql =
                "SELECT " +
                        "  strftime('%s',entrydate), " +
                        "  SUM(COALESCE(sel.RealSum, 0)), " +
                        "  SUM(COALESCE(sel.PlanSum, 0)) " +
                        "FROM (" +
                        "  SELECT " +
                        "    d.entrydate, " +
                        "    SUM(d.debitncu) AS RealSum, " +
                        "    0 AS PlanSum " +
                        "  FROM mb_contact a " +
                        "  JOIN mb_entry d ON d.creditcontactkey = a.id " +
                        "                 AND d.debit = '51'  " +
                        "  WHERE a.ID = '" + id + "' " +
                        "  GROUP BY 1,3 " +
                        " " +
                        "  UNION ALL " +
                        " " +
                        "  SELECT " +
                        "    d.entrydate, " +
                        "    0 AS RealSum, " +
                        "    SUM(d.debitncu) AS PlanSum " +
                        "  FROM mb_contact a " +
                        "  JOIN mb_entry d ON d.debitcontactkey = a.id " +
                        "                 AND d.debit = 'PLAN'" +
                        "  WHERE a.ID = '" + id + "' " +
                        "  GROUP BY 1,2 " +
                        "  HAVING SUM(d.debitncu) > 0) sel " +
                        "GROUP BY 1 " +
                        "HAVING SUM(COALESCE(sel.RealSum, 0)) <> 0 OR SUM(COALESCE(sel.PlanSum, 0)) <> 0 " +
                        "ORDER BY entrydate";

        Cursor cursor = mDB.rawQuery(sql, null);
        Calendar cal;
        List<Calendar> possibleDates = new ArrayList<Calendar>();
        if (cursor.moveToFirst()) {
            do {
                cal = Calendar.getInstance();
                cal.setTimeInMillis(cursor.getLong(0) * 1000);
                possibleDates.add(cal);
            } while (cursor.moveToNext());

        } else return null;

        return calculateSlidingDates(possibleDates, slidingDate);
    }

    private List<Calendar> calculateSlidingDates(List<Calendar> possibleDatesList, int slidingDate) {
        Calendar firstDate;
        Calendar secondDate;

        List<Calendar> slidingDatesList = new ArrayList<Calendar>();
        slidingDatesList.add((Calendar) possibleDatesList.get(0).clone());
        for (int i = 0; i < possibleDatesList.size(); i++) {

            firstDate = (Calendar) possibleDatesList.get(i).clone();
            Utils.initCalendarFields(firstDate, slidingDate, 1);
            firstDate.add(slidingDate, 1);

            for (int j = i + 1; j < possibleDatesList.size(); j++) {

                secondDate = (Calendar) possibleDatesList.get(j).clone();
                Utils.initCalendarFields(secondDate, slidingDate, 1);

                if (firstDate.compareTo(secondDate) <= 0) {
                    i = j - 1;
                    slidingDatesList.add(secondDate);
                    break;
                } else if (j == possibleDatesList.size() - 1) i = j - 1;
            }
        }
        return slidingDatesList;
    }

    /**
     * 1. Расчетный счет
     * (Меню - Расчетный счет)
     * исп. в BalanceFrgm
     * остаток на расчетном счету
     * 51 - оплаты от клиентов нашей организации (на дебет) или оплата нашей орг. клиентам (на кредите)
     * 51 – Консолидированный счет  для всех рублевых и валютных банковских счетов предприятия.
     * Валютные суммы пересчитываются в рубли по курсу на
     * день операции (это подготовлено еще до синхронизации с сервером должно быть).
     * <p/>
     * ПРОВЕРЕНО
     */
    public long getBalance() {
        String sql =
                "SELECT COALESCE(d.S,0) - COALESCE(c.S, 0) " +
                        "FROM (" +
                        "   SELECT SUM(debitncu) AS S " +
                        "   FROM mb_entry " +
                        "   WHERE debit = '51') d " +
                        "LEFT JOIN (" +
                        "   SELECT SUM(creditncu) AS S " +
                        "   FROM mb_entry " +
                        "   WHERE credit = '51') c " +
                        "      ON 1 = 1";

        Cursor cursor = mDB.rawQuery(sql, null);

        if (cursor.moveToFirst())
            return cursor.getLong(0);
        else
            return 0;
    }

    /**
     * 2. Поступления
     * (Меню - Поступления,
     * Меню - Календарь - Месяц - Поступления)
     * исп. в IncomeFragment
     * Список всех поступлений (от всех клиентов) на расчетный счет.
     * 2.1. Выводится на конкретную дату
     * 2.2. В таблице две колонки: Наименование клиента, Сумма.
     * 2.3. Записи отсортированы по сумме перечисления, в убывающем порядке.
     * <p/>
     * ПРОВЕРЕНО!
     */
    public List<ClientsTable> getClientsIncomeList(int day, int month, int year) {
        String OnDate = year + "-" + AddZero(month) + "-" + AddZero(day);
        List<ClientsTable> contactList = new ArrayList<ClientsTable>();
        String sql =
                "SELECT " +
                        "  sel.ID, " +
                        "  sel.NAME, " +
                        "  SUM(COALESCE(sel.RealSum, 0)) AS RealSum, " +
                        "  SUM(COALESCE(sel.PlanSum, 0)) AS PlanSum " +
                        "FROM (" +
                        "  SELECT " +
                        "    a.id, " +
                        "    a.name, " +
                        "    SUM(d.debitncu) AS RealSum, " +
                        "    0 AS PlanSum " +
                        "  FROM mb_contact a " +
                        "  JOIN mb_entry d ON d.creditcontactkey = a.id " +
                        "                 AND d.debit = '51'   " +
                        "                 AND d.entrydate = DATE('" + OnDate + "') " +
                        "  GROUP BY 1,2,4 " +
                        " " +
                        "  UNION ALL " +
                        " " +
                        "  SELECT " +
                        "    a.id, " +
                        "    a.name, " +
                        "    0 AS RealSum, " +
                        "    SUM(d.debitncu) AS PlanSum " +
                        "  FROM mb_contact a " +
                        "  JOIN mb_entry d ON d.debitcontactkey = a.id " +
                        "                 AND d.debit = 'PLAN'" +
                        "                 AND d.entrydate = DATE('" + OnDate + "') " +
                        "  GROUP BY 1,2,3 " +
                        "  HAVING SUM(d.debitncu) > 0 ) sel " +
                        "GROUP BY 1, 2 " +
                        "ORDER BY 2 ";
        Cursor cursor = mDB.rawQuery(sql, null);
        ClientsTable contact;
        if (cursor.moveToFirst()) {
            do {
                contact = new ClientsTable(cursor.getInt(0), cursor.getString(1), cursor.getLong(2), cursor.getLong(3), year, month, day);
                contactList.add(contact);
            } while (cursor.moveToNext());
        }
        return contactList;
    }

    /**
     * 2A. Поступления - сумма (для вывода вверху экрана посуплений)
     * (Меню - Поступления,
     * Меню - Календарь - Месяц - Поступления)
     * исп. в IncomeFragment
     * Список всех поступлений (от всех клиентов) на расчетный счет.
     * 2A.1. Выводится на конкретную дату
     * 2A.2. В таблице две колонки: Наименование клиента, Сумма.
     * 2A.3. Записи отсортированы по сумме перечисления, в убывающем порядке.
     * <p/>
     * NOT ПРОВЕРЕНО!
     */
    public ClientsTable getIncomeSum(int day, int month, int year) {
        String OnDate = year + "-" + AddZero(month) + "-" + AddZero(day);
        String sql =
                "SELECT " +
                        "  SUM(COALESCE(sel.RealSum, 0)) AS RealSum, " +
                        "  SUM(COALESCE(sel.PlanSum, 0)) AS PlanSum " +
                        "FROM (" +
                        "  SELECT " +
                        "    a.id, " +
                        "    SUM(d.debitncu) AS RealSum, " +
                        "    0 AS PlanSum " +
                        "  FROM mb_contact a " +
                        "  JOIN mb_entry d ON d.creditcontactkey = a.id " +
                        "                 AND d.debit = '51'   " +
                        "                 AND d.entrydate = DATE('" + OnDate + "') " +
                        "  GROUP BY 1,3 " +
                        " " +
                        "  UNION ALL " +
                        " " +
                        "  SELECT " +
                        "    a.id, " +
                        "    0 AS RealSum, " +
                        "    SUM(d.debitncu) AS PlanSum " +
                        "  FROM mb_contact a " +
                        "  JOIN mb_entry d ON d.debitcontactkey = a.id " +
                        "                 AND d.debit = 'PLAN'" +
                        "                 AND d.entrydate = DATE('" + OnDate + "') " +
                        "  GROUP BY 1,2 " +
                        "  HAVING SUM(d.debitncu) > 0) sel ";
        Cursor cursor = mDB.rawQuery(sql, null);
        if (cursor.moveToFirst()) {
            return new ClientsTable(0, getDateString(context, year, month, day), cursor.getLong(0), cursor.getLong(1), year, month, day);
        }
        return new ClientsTable(0, getDateString(context, year, month, day), 0, 0);
    }

    /**
     * 3. Клиенты
     * (Меню - За месяц)
     * исп. в ClientsMonthListFrgm
     * 3.1. За выбранный месяц
     * 3.2. В списке отображаются только клиенты, которые в указанном месяце платили
     * предприятию. Изначально список открывается для текущего месяца.
     * 3.3. Записи отсортированы в алфавитном порядке.
     * 3.4. Список содержит: Наименование, Оплаченную сумму, Плановую сумму (если она есть), Процент.
     * 62 – расчеты с покупателями.
     * 008 – для плановых поступлений.
     * <p/>
     * ПРОВЕРЕНО
     */
    public List<ClientsTable> getClientsMonthList(int month, int year) {
        List<ClientsTable> contactList = new ArrayList<ClientsTable>();
        String sql =
                "SELECT " +
                        "  sel.ID, " +
                        "  sel.NAME, " +
                        "  SUM(COALESCE(sel.RealSum, 0)), " +
                        "  SUM(COALESCE(sel.PlanSum, 0)) " +
                        "FROM (" +
                        "  SELECT " +
                        "    a.id, " +
                        "    a.name, " +
                        "    SUM(d.debitncu) AS RealSum, " +
                        "    0 AS PlanSum " +
                        "  FROM mb_contact a " +
                        "  JOIN mb_entry d ON d.creditcontactkey = a.id " +
                        "                 AND d.debit = '51'   " +
                        "                 AND strftime('%Y', d.entrydate)='" + year + "' " +
                        "                 AND strftime('%m', d.entrydate)='" + AddZero(month) + "' " +
                        "  GROUP BY 1,2,4 " +
                        " " +
                        "  UNION ALL " +
                        " " +
                        "  SELECT " +
                        "    a.id, " +
                        "    a.name, " +
                        "    0 AS RealSum, " +
                        "    SUM(d.debitncu) AS PlanSum " +
                        "  FROM mb_contact a " +
                        "  JOIN mb_entry d ON d.debitcontactkey = a.id " +
                        "                 AND d.debit = 'PLAN'" +
                        "                 AND strftime('%Y', d.entrydate)='" + year + "' " +
                        "                 AND strftime('%m', d.entrydate)='" + AddZero(month) + "' " +
                        "  GROUP BY 1,2,3 " +
                        "  HAVING SUM(d.debitncu) > 0) sel " +
                        "GROUP BY 1, 2 " +
                        "ORDER BY 2 /* 3 DESC */";
        Cursor cursor = mDB.rawQuery(sql, null);

        if (cursor.moveToFirst()) {
            do {
                ClientsTable contact = new ClientsTable(cursor.getInt(0), cursor.getString(1),
                        cursor.getLong(2), cursor.getLong(3), year, month);

                contactList.add(contact);
            } while (cursor.moveToNext());
        }
        return contactList;
    }

    public ClientsTable getClientsMonthSum(int month, int year) {
        String sql =
                "SELECT " +
                        "  SUM(COALESCE(sel.RealSum, 0)), " +
                        "  SUM(COALESCE(sel.PlanSum, 0)) " +
                        "FROM (" +
                        "  SELECT " +
                        "    SUM(d.debitncu) AS RealSum, " +
                        "    0 AS PlanSum " +
                        "  FROM mb_contact a " +
                        "  JOIN mb_entry d ON d.creditcontactkey = a.id " +
                        "                 AND d.debit = '51'" +
                        "                 AND strftime('%Y', d.entrydate)='" + year + "' " +
                        "                 AND strftime('%m', d.entrydate)='" + AddZero(month) + "' " +
                        "  GROUP BY 2 " +
                        " " +
                        "  UNION ALL " +
                        " " +
                        "  SELECT " +
                        "    0 AS RealSum, " +
                        "    SUM(d.debitncu) AS PlanSum " +
                        "  FROM mb_contact a " +
                        "  JOIN mb_entry d ON d.debitcontactkey = a.id " +
                        "                 AND d.debit = 'PLAN'" +
                        "                 AND strftime('%Y', d.entrydate)='" + year + "' " +
                        "                 AND strftime('%m', d.entrydate)='" + AddZero(month) + "' " +
                        "  GROUP BY 1 " +
                        "  HAVING SUM(d.debitncu) > 0) sel ";
        Cursor cursor = mDB.rawQuery(sql, null);
        ClientsTable objIncome;
        if (cursor.moveToFirst()) {
            objIncome = new ClientsTable(month, cursor.getLong(0), cursor.getLong(1), year, month);
            objIncome.convertMonthYear(context, year);
            return objIncome;
        }
        objIncome = new ClientsTable(month, 0, 0);
        objIncome.convertMonthYear(context, year);
        return objIncome;
    }

    /**
     * 3. Клиенты
     * (Меню - Клиенты)
     * исп. в ClientsListFrgm
     * 3.1. За весь период
     * 3.2. В списке отображаются только клиенты, которые платили
     * предприятию.
     * 3.3. Записи отсортированы в алфавитном порядке.
     * 3.4. Список содержит: Наименование, Оплаченную сумму, Плановую сумму (если она есть), Процент.
     * 62 – расчеты с покупателями.
     */
    public List<ClientsTable> getClientsList() {
        List<ClientsTable> contactList = new ArrayList<ClientsTable>();
        String sql =
                "SELECT " +
                        "  sel.ID, " +
                        "  sel.NAME, " +
                        "  SUM(COALESCE(sel.RealSum, 0)), " +
                        "  SUM(COALESCE(sel.PlanSum, 0)) " +
                        "FROM (" +
                        "  SELECT " +
                        "    a.id, " +
                        "    a.name, " +
                        "    SUM(d.debitncu) AS RealSum, " +
                        "    0 AS PlanSum " +
                        "  FROM mb_contact a " +
                        "  JOIN mb_entry d ON d.creditcontactkey = a.id " +
                        "                 AND d.debit = '51'   " +
                        "  GROUP BY 1,2,4 " +
                        " " +
                        "  UNION ALL " +
                        " " +
                        "  SELECT " +
                        "    a.id, " +
                        "    a.name, " +
                        "    0 AS RealSum, " +
                        "    SUM(d.debitncu) AS PlanSum " +
                        "  FROM mb_contact a " +
                        "  JOIN mb_entry d ON d.debitcontactkey = a.id " +
                        "                 AND d.debit = 'PLAN'" +
                        "  GROUP BY 1,2,3 " +
                        "  HAVING SUM(d.debitncu) > 0) sel " +
                        "GROUP BY 1, 2 " +
                        "HAVING SUM(sel.RealSum) > 0 OR SUM(sel.PlanSum) > 0 " +
                        "ORDER BY 2";
        Cursor cursor = mDB.rawQuery(sql, null);

        if (cursor.moveToFirst()) {
            do {
                ClientsTable contact = new ClientsTable(cursor.getInt(0), cursor.getString(1),
                        cursor.getLong(2), cursor.getLong(3));

                contactList.add(contact);
            } while (cursor.moveToNext());
        }
        return contactList;
    }

    /**
     * 4. Клиент
     * (Меню - Поступления - Клиент,
     * Меню - За месяц - Клиент,
     * Меню - Календарь - Месяц - Поступления - Клиент)
     * исп. в ClientYearFragment
     * 4.1. За год.
     * 4.2. Вверху экрана выводится наименование клиента
     * 4.3. Таблица за выбранный год. Каждая запись таблицы один
     * месяц. Таблица имеет две колонки: месяц, сумма. В колонке Сумма выводится фактическая
     * перечисленная сумма, плановая сумма и процент.
     * 4.4. Сортировка в хронологическом порядке.
     * ПРОВЕРЕНО
     */
    public List<ClientsTable> getClientItemList(int id, int year) {

        List<ClientsTable> contactList = new ArrayList<ClientsTable>();
        String sql =
                "SELECT " +
                        "  sel.CMonth, " +
                        "  SUM(COALESCE(sel.RealSum, 0)), " +
                        "  SUM(COALESCE(sel.PlanSum, 0)) " +
                        "FROM (" +
                        "  SELECT " +
                        "    strftime('%m', d.entrydate) AS CMonth, " +
                        "    SUM(d.debitncu) AS RealSum, " +
                        "    0 AS PlanSum " +
                        "  FROM mb_contact a " +
                        "  JOIN mb_entry d ON d.creditcontactkey = a.id " +
                        "                 AND d.debit = '51'  " +
                        "                 AND strftime('%Y', d.entrydate)='" + year + "' " +
                        "  WHERE a.ID = '" + id + "' " +
                        "  GROUP BY 1,3 " +
                        " " +
                        "  UNION ALL " +
                        " " +
                        "  SELECT " +
                        "    strftime('%m', d.entrydate) AS CMonth, " +
                        "    0 AS RealSum, " +
                        "    SUM(d.debitncu) AS PlanSum " +
                        "  FROM mb_contact a " +
                        "  JOIN mb_entry d ON d.debitcontactkey = a.id " +
                        "                 AND d.debit = 'PLAN'" +
                        "                 AND strftime('%Y', d.entrydate)='" + year + "' " +
                        "  WHERE a.ID = '" + id + "' " +
                        "  GROUP BY 1,2 " +
                        "  HAVING SUM(d.debitncu) > 0) sel " +
                        "GROUP BY 1 " +
                        "ORDER BY 1";

        Cursor cursor = mDB.rawQuery(sql, null);

        ClientsTable contact;
        if (cursor.moveToFirst()) {
            do {
                long paidSum = cursor.getLong(1);
                long planSum = cursor.getLong(2);
                if (paidSum == 0 && planSum == 0)
                    continue;
                contact = new ClientsTable((int) cursor.getDouble(0), paidSum, planSum, year, (int) cursor.getDouble(0));
                contact.convertMonth(context);
                contactList.add(contact);
            } while (cursor.moveToNext());
        }
        new ClientsTableHelper(context).addTotalSum(contactList, year);
        return contactList;
    }

    /**
     * 5. Движение по клиенту за месяц
     * (Меню - Поступления - Клиент - Движение,
     * Меню - За месяц - Клиент - Движение,
     * Меню - Календарь - Месяц - Поступления - Клиент - Движение)
     * исп. в ClientMonthFragment
     * В верхней части выводится наименование клиента. Показываются фактическая и плановая суммы за месяц, процент.
     * Ниже выводятся все оплаты клиента в хронологическом порядке за указанный месяц.
     * Таблица имеет две колонки: Дата, Сумма оплаты.
     * ПРОВЕРЕНО
     */
    public List<ClientsTable> getClientItemMonthList(int id, int month, int year) {   // Временно
        List<ClientsTable> contactList = new ArrayList<ClientsTable>();
        String sql =
                "SELECT " +
                        " strftime('%d', d.entrydate)," +
                        " strftime('%m', d.entrydate)," +
                        " strftime('%Y', d.entrydate)," +
                        " d.debitncu AS RealSum " +
                        "FROM mb_contact a " +
                        "JOIN mb_entry d ON d.creditcontactkey = a.id " +
                        "               AND d.debit = '51'  " +
                        "               AND strftime('%Y', d.entrydate)='" + year + "' " +
                        "               AND strftime('%m', d.entrydate)='" + AddZero(month) + "' " +
                        "WHERE a.ID = " + id + " AND d.debitncu <> 0 " +
                        "ORDER BY 1";

        Cursor cursor = mDB.rawQuery(sql, null);

        if (cursor.moveToFirst()) {
            do {
                ClientsTable contact = new ClientsTable(id,
                        getDateString(context, (int) cursor.getDouble(2), (int) cursor.getDouble(1), (int) cursor.getDouble(0)),
                        cursor.getLong(3) /*, 0, year, month, (int) cursor.getDouble(0)*/);
                contactList.add(contact);
            } while (cursor.moveToNext());
        }
        return contactList;
    }

    /**
     * 5A. Движение по клиенту за месяц
     * (Меню - Поступления - Клиент - Движение,
     * Меню - За месяц - Клиент - Движение,
     * Меню - Календарь - Месяц - Поступления - Клиент - Движение)
     * исп. в ClientMonthFragment
     * В верхней части выводится наименование клиента. Показываются фактическая и плановая суммы за месяц, процент.
     * Ниже выводятся все оплаты клиента в хронологическом порядке за указанный месяц.
     * Таблица имеет две колонки: Дата, Сумма оплаты.
     * ПРОВЕРЕНО
     */
    public ClientsTable getClientItemMonthListSum(int id, int month, int year) {

        String sql =
                "SELECT " +
                        "  SUM(COALESCE(sel.RealSum, 0)), " +
                        "  SUM(COALESCE(sel.PlanSum, 0)) " +
                        "FROM (" +
                        "  SELECT " +
                        "    SUM(d.debitncu) AS RealSum, " +
                        "    0 AS PlanSum " +
                        "  FROM mb_contact a " +
                        "  JOIN mb_entry d ON d.creditcontactkey = a.id " +
                        "                 AND d.debit = '51'" +
                        "                 AND strftime('%Y', d.entrydate)='" + year + "' " +
                        "                 AND strftime('%m', d.entrydate)='" + AddZero(month) + "' " +
                        "  WHERE a.ID = '" + id + "' " +
                        "  GROUP BY 2 " +
                        " " +
                        "  UNION ALL " +
                        " " +
                        "  SELECT " +
                        "    0 AS RealSum, " +
                        "    SUM(d.debitncu) AS PlanSum " +
                        "  FROM mb_contact a " +
                        "  JOIN mb_entry d ON d.debitcontactkey = a.id " +
                        "                 AND d.debit = 'PLAN'" +
                        "                 AND strftime('%Y', d.entrydate)='" + year + "' " +
                        "                 AND strftime('%m', d.entrydate)='" + AddZero(month) + "' " +
                        "  WHERE a.ID = " + id + " " +
                        "  GROUP BY 1 " +
                        "  HAVING SUM(d.debitncu) > 0) sel ";


        Cursor cursor = mDB.rawQuery(sql, null);
        ClientsTable objIncome;
        if (!cursor.moveToFirst())
            objIncome = new ClientsTable(month, 0, 0, year, month);
        else
            objIncome = new ClientsTable(month, cursor.getLong(0), cursor.getLong(1), year, month);

        objIncome.convertMonthYear(context, year);
        return objIncome;
    }

    /**
     * 6. Календарь
     * (Меню - Календарь)
     * исп. в CalendarFragment
     * 6.1. На Action Bar располагается фильтр для выбора номера года. По умолчанию
     * открывается текущий год.
     * 6.2. На экране располагается список. В списке выводится
     * краткое наименование месяца, сумма (факт, план, процент). Цвет фона записи
     * показывает соотношение факта к плану. Если нет данных или суммы поступлений и плана нули, ничего не выводит
     * 6.3. Тач по месяцу переводит на экран Месяц.
     * ************************
     * список месяцев, открывается после выбора года в меню Календарь;
     * если год не выбран, то берется текущий;
     * если проводок по месяцу нет, то все равно выводится с нулевыми суммами
     */
    public List<ClientsTable> getCalendarList(int year) {

        List<ClientsTable> contactList = new ArrayList<ClientsTable>();
        String sql =
                "SELECT " +
                        "  sel.CMonth, " +
                        "  SUM(COALESCE(sel.RealSum, 0)), " +
                        "  SUM(COALESCE(sel.PlanSum, 0)) " +
                        "FROM (" +
                        "  SELECT " +
                        "    strftime('%m', d.entrydate) AS CMonth, " +
                        "    SUM(d.debitncu) AS RealSum, " +
                        "    0 AS PlanSum " +
                        "  FROM mb_contact a " +
                        "  JOIN mb_entry d ON d.creditcontactkey = a.ID " +
                        "                 AND d.debit = '51'  " +
                        "                 AND strftime('%Y', d.entrydate)='" + year + "'  " +
                        "  GROUP BY 1,3 " +
                        " " +
                        "  UNION ALL " +
                        " " +
                        "  SELECT " +
                        "    strftime('%m', d.entrydate) AS CMonth, " +
                        "    0 AS RealSum, " +
                        "    SUM(d.debitncu) AS PlanSum " +
                        "  FROM mb_contact a " +
                        "  JOIN mb_entry d ON d.debitcontactkey = a.id " +
                        "                 AND d.debit = 'PLAN'" +
                        "                 AND strftime('%Y', d.entrydate)='" + year + "'   " +
                        "  GROUP BY 1,2" +
                        "  HAVING SUM(d.debitncu) > 0) sel " +
                        "GROUP BY 1 " +
                        "ORDER BY 1";

        Cursor cursor = mDB.rawQuery(sql, null);
        ClientsTable contact;
        if (cursor.moveToFirst()) {
            do {
                long paidSum = cursor.getLong(1);
                long planSum = cursor.getLong(2);
                if (paidSum == 0 && planSum == 0)
                    continue;
                contact = new ClientsTable((int) cursor.getDouble(0), paidSum, planSum, year, (int) cursor.getDouble(0));
                contact.convertMonth(context);
                contactList.add(contact);
            } while (cursor.moveToNext());
        }
        new ClientsTableHelper(context).addTotalSum(contactList, year);
        return contactList;
    }

    /**
     * 7. График
     * (Меню - Графики)
     * исп. в GraphFragment
     * 7.1. На Action Bar располагается фильтр для выбора номера года. По умолчанию
     * открывается текущий год.
     * 7.2. На экране располагается список график состоящий из 12 месяцев
     * ************************
     * список месяцев, открывается после выбора года в меню Календарь;
     * если год не выбран, то берется текущий;
     * если проводок по месяцу нет, то все равно выводится с нулевыми суммами
     */
    public List<ClientsTable> getGraphList(int year) {

        List<ClientsTable> contactList = new ArrayList<ClientsTable>();
        String sql =
                "SELECT " +
                        "  sel.CMonth, " +
                        "  SUM(COALESCE(sel.RealSum, 0)), " +
                        "  SUM(COALESCE(sel.PlanSum, 0)) " +
                        "FROM (" +
                        "  SELECT " +
                        "    strftime('%m', d.entrydate) AS CMonth, " +
                        "    SUM(d.debitncu) AS RealSum, " +
                        "    0 AS PlanSum " +
                        "  FROM mb_contact a " +
                        "  JOIN mb_entry d ON d.creditcontactkey = a.ID " +
                        "                 AND d.debit = '51'  " +
                        "                 AND strftime('%Y', d.entrydate)='" + year + "'  " +
                        "  GROUP BY 1,3 " +
                        " " +
                        "  UNION ALL " +
                        " " +
                        "  SELECT " +
                        "    strftime('%m', d.entrydate) AS CMonth, " +
                        "    0 AS RealSum, " +
                        "    SUM(d.debitncu) AS PlanSum " +
                        "  FROM mb_contact a " +
                        "  JOIN mb_entry d ON d.debitcontactkey = a.id " +
                        "                 AND d.debit = 'PLAN'" +
                        "                 AND strftime('%Y', d.entrydate)='" + year + "'   " +
                        "  GROUP BY 1,2 " +
                        "  HAVING SUM(d.debitncu) > 0 ) sel " +
                        "GROUP BY 1 " +
                        "ORDER BY 1";

        Cursor cursor = mDB.rawQuery(sql, null);
        boolean isData = cursor.moveToFirst();
        int iMonth = 0;
        ClientsTable contact;
        do {
            iMonth++;

            if (isData) {
                if (((int) cursor.getDouble(0) == iMonth)) {
                    contact = new ClientsTable(iMonth, cursor.getLong(1), cursor.getLong(2), year, iMonth);
                    isData = cursor.moveToNext();
                } else {
                    contact = new ClientsTable(iMonth, 0L, 0L, year, iMonth);
                }
            } else {
                contact = new ClientsTable(iMonth, 0L, 0L, year, iMonth);
            }
            contact.convertMonth(context);
            contactList.add(contact);
        } while (iMonth < 12);

        return contactList;
    }

    /**
     * 8. Месяц
     * (Меню - Календарь - Месяц)
     * исп.- CalendarMonthFragment
     * 8.1. Календарная сетка месяца по неделям. Для выходных и праздничных дней
     * используется штриховка фона.
     * 8.2. В клетке показывается число.
     * 8.3. Цвет фона показывает соотношение факт/план.
     * 8.4. Тач по клетке переводит на экран Поступления.
     * 8.5. На Action Bar располагаются команды Пред месяц, След месяц.
     */
    public List<ClientsTable> getCalendarMonthList(int month, int year) {   // Временно
        List<ClientsTable> contactList = new ArrayList<ClientsTable>();
        String sql =
                "SELECT " +
                        "  sel.CDay, " +
                        "  SUM(COALESCE(sel.RealSum, 0)), " +
                        "  SUM(COALESCE(sel.PlanSum, 0)) " +
                        "FROM (" +
                        "  SELECT " +
                        "    strftime('%d', d.entrydate) AS CDay, " +
                        "    SUM(d.debitncu) AS RealSum, " +
                        "    0 AS PlanSum " +
                        "  FROM mb_contact a " +
                        "  JOIN mb_entry d ON d.creditcontactkey = a.ID " +
                        "                 AND d.debit = '51'  " +
                        "                 AND strftime('%Y', d.entrydate)='" + year + "'  " +
                        "                 AND strftime('%m', d.entrydate)='" + AddZero(month) + "' " +
                        "  GROUP BY 1,3 " +
                        " " +
                        "  UNION ALL " +
                        " " +
                        "  SELECT " +
                        "    strftime('%d', d.entrydate) AS CDay, " +
                        "    0 AS RealSum, " +
                        "    SUM(d.debitncu) AS PlanSum " +
                        "  FROM mb_contact a " +
                        "  JOIN mb_entry d ON d.debitcontactkey = a.id " +
                        "                 AND d.debit = 'PLAN'" +
                        "                 AND strftime('%Y', d.entrydate)='" + year + "'   " +
                        "                 AND strftime('%m', d.entrydate)='" + AddZero(month) + "' " +
                        "  GROUP BY 1,2 " +
                        "  HAVING SUM(d.debitncu) > 0) sel " +
                        "GROUP BY 1 " +
                        "ORDER BY 1";
        Cursor cursor = mDB.rawQuery(sql, null);
        boolean isData = cursor.moveToFirst();

        Calendar calendar = Calendar.getInstance();
        calendar.set(year, month - 1, 1);
        int numDays = calendar.getActualMaximum(Calendar.DATE);

        int iDays = 0;
        ClientsTable contact;

        do {
            iDays++;
            calendar.set(year, month - 1, iDays);

            if (isData) {
                if (((int) cursor.getDouble(0) == iDays)) {
                    contact = new ClientsTable((int) cursor.getDouble(0), (int) cursor.getDouble(0) + ", " +
                            getDayOfWeek(context, calendar.get(Calendar.DAY_OF_WEEK)), cursor.getLong(1), cursor.getLong(2), year, month, iDays);
                    isData = cursor.moveToNext();

                } else {
                    contact = new ClientsTable((iDays), String.valueOf(iDays) + ", " +
                            getDayOfWeek(context, calendar.get(Calendar.DAY_OF_WEEK)), 0L, 0L, year, month, iDays);
                }
            } else {
                contact = new ClientsTable((iDays), String.valueOf(iDays) + ", " +
                        getDayOfWeek(context, calendar.get(Calendar.DAY_OF_WEEK)), 0L, 0L, year, month, iDays);
            }

            contactList.add(contact);
        } while (iDays < numDays);
        new ClientsTableHelper(context).addTotalSum(contactList, month, year);
        return contactList;
    }

    public String[] getStatistic() {
        String[] line = new String[3];

        Cursor cursor = mDB.rawQuery("SELECT COUNT(*) FROM MB_CONTACT", null);
        cursor.moveToFirst();
        line[0] = cursor.getString(0);

        cursor = mDB.rawQuery("SELECT COUNT(*) FROM MB_ENTRY", null);
        cursor.moveToFirst();
        line[1] = cursor.getString(0);

        line[2] = String.valueOf(getVerData());

        return line;
    }

    /**
     * конвертирует int в String, если число меньше 10, то добавляем к нему 0 (пример: int 8 -> String 08)
     */
    public static String AddZero(int V) {
        if (V < 10)
            return "0" + String.valueOf(V);
        else
            return String.valueOf(V);
    }

    /**
     * @return строка формата dd.mm.yyyy, dayOfWeek(localized)
     */
    public static String getDateString(Context context, int year, int month, int day) {
        Calendar cl = Calendar.getInstance();
        cl.set(year, month - 1, day);
        return AddZero(cl.get(Calendar.DAY_OF_MONTH)) + "." + AddZero(cl.get(Calendar.MONTH) + 1) + "." +
                cl.get(Calendar.YEAR) + ", " + getDayOfWeek(context, cl.get(Calendar.DAY_OF_WEEK));
    }

    /**
     * @return имя дня недели по номеру. Имена хранятся в xml файле strings в массиве day_list_values
     */
    public static String getDayOfWeek(Context context, int DoW) {
        String[] ArrayDoW = context.getResources().getStringArray(R.array.day_list_values);
        return ArrayDoW[DoW - 1];
    }
}